﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Text;

/// <summary>
/// Descrizione di riepilogo per SqlHelper
/// </summary>
namespace ServiceLibrary.DLayer
{
    public static class SqlHelper
    {
        public static List<T> ExecuteStoredProcedure<T>(this ObjectContext dbContext, string storedProcedureName, params SqlParameter[] parameters)
        {
            var spSignature = new StringBuilder();
            object[] spParameters;
            bool hasTableVariables = parameters.Any(p => p.SqlDbType == SqlDbType.Structured);

            spSignature.AppendFormat("EXECUTE {0}", storedProcedureName);
            var length = parameters.Count() - 1;

            if (hasTableVariables)
            {
                var tableValueParameters = new List<SqlParameter>();

                for (int i = 0; i < parameters.Count(); i++)
                {
                    switch (parameters[i].SqlDbType)
                    {
                        case SqlDbType.Structured:
                            spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
                            tableValueParameters.Add(parameters[i]);
                            break;
                        case SqlDbType.VarChar:
                        case SqlDbType.Char:
                        case SqlDbType.Text:
                        case SqlDbType.NVarChar:
                        case SqlDbType.NChar:
                        case SqlDbType.NText:
                        case SqlDbType.Xml:
                        case SqlDbType.UniqueIdentifier:
                        case SqlDbType.Time:
                        case SqlDbType.Date:
                        case SqlDbType.DateTime:
                        case SqlDbType.DateTime2:
                        case SqlDbType.DateTimeOffset:
                        case SqlDbType.SmallDateTime:
                            // TODO: some magic here to avoid SQL injections
                            spSignature.AppendFormat(" '{0}'", parameters[i].Value.ToString());
                            break;
                        default:
                            spSignature.AppendFormat(" {0}", parameters[i].Value.ToString());
                            break;
                    }

                    if (i != length) spSignature.Append(",");
                }
                spParameters = tableValueParameters.Cast<object>().ToArray();
            }
            else
            {
                for (int i = 0; i < parameters.Count(); i++)
                {
                    spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
                    if (i != length) spSignature.Append(",");
                }
                spParameters = parameters.Cast<object>().ToArray();
            }

            var query = dbContext.ExecuteStoreQuery<T>(spSignature.ToString(), spParameters);


            var list = query.ToList();
            return list;
        }

        public static string ExecuteStoredProcedure_ToString<T>(this ObjectContext dbContext, string storedProcedureName, params SqlParameter[] parameters)
        {
            var spSignature = new StringBuilder();
            object[] spParameters;
            bool hasTableVariables = parameters.Any(p => p.SqlDbType == SqlDbType.Structured);

            spSignature.AppendFormat("EXECUTE {0}", storedProcedureName);
            var length = parameters.Count() - 1;

            if (hasTableVariables)
            {
                var tableValueParameters = new List<SqlParameter>();

                for (int i = 0; i < parameters.Count(); i++)
                {
                    switch (parameters[i].SqlDbType)
                    {
                        case SqlDbType.Structured:
                            spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
                            tableValueParameters.Add(parameters[i]);
                            break;
                        case SqlDbType.VarChar:
                        case SqlDbType.Char:
                        case SqlDbType.Text:
                        case SqlDbType.NVarChar:
                        case SqlDbType.NChar:
                        case SqlDbType.NText:
                        case SqlDbType.Xml:
                        case SqlDbType.UniqueIdentifier:
                        case SqlDbType.Time:
                        case SqlDbType.Date:
                        case SqlDbType.DateTime:
                        case SqlDbType.DateTime2:
                        case SqlDbType.DateTimeOffset:
                        case SqlDbType.SmallDateTime:
                            // TODO: some magic here to avoid SQL injections
                            spSignature.AppendFormat(" '{0}'", parameters[i].Value.ToString());
                            break;
                        default:
                            spSignature.AppendFormat(" {0}", parameters[i].Value.ToString());
                            break;
                    }

                    if (i != length) spSignature.Append(",");
                }
                spParameters = tableValueParameters.Cast<object>().ToArray();
            }
            else
            {
                for (int i = 0; i < parameters.Count(); i++)
                {
                    spSignature.AppendFormat(" @{0}", parameters[i].ParameterName);
                    if (i != length) spSignature.Append(",");
                }
                spParameters = parameters.Cast<object>().ToArray();
            }

            ObjectResult<string> query = dbContext.ExecuteStoreQuery<string>(spSignature.ToString(), spParameters);

            var list = "";

            foreach (string p in query)
            {
                list += p.ToString();
            }

            return list;
        }
    }
}